﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
namespace Pentakill
{
    public partial class Customers : Form
    {
        SqlConnection con = new SqlConnection();
        SqlDataAdapter da;
        DataSet ds;
        SqlCommandBuilder cb;
        DataTable dt;
        DataView dv;
        

        public Customers()
        {
            
            InitializeComponent();
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            con.ConnectionString = constr;    
            InitializeADOOObject();
            LoadCustomerInfo();
            errorProvider1.Clear();
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;

            

        }

#region add, delete,update, new, save
        void InitializeADOOObject()
        {

            
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "getCustomerTable";

            da = new SqlDataAdapter(cmd);
            cb = new SqlCommandBuilder(da);

            ds = new DataSet();
            da.FillSchema(ds, SchemaType.Mapped);
            da.Fill(ds, "Sales.Customers");
         
            

        }
        
        bool validateInput()
        {
            errorProvider1.Clear();
            string copname = txtCopname.Text;
            string conname = txtConname.Text;
            string title = txtTitle.Text;
            string address = txtAddress.Text;
            string city = txtCity.Text;


            bool error = false;
            if (copname.Trim().Length == 0)
            {
                errorProvider1.SetError(txtCopname, "Company Name is required.");
                error = true;
            }
            if (conname.Trim().Length == 0)
            {
                errorProvider1.SetError(txtConname, "Contact Name is required.");
                error = true;
            }
            if (title.Trim().Length == 0)
            {
                errorProvider1.SetError(txtTitle, "Contact title is required.");
                error = true;
            }
            if (address.Trim().Length == 0)
            {
                errorProvider1.SetError(txtAddress, "Address is required.");
                error = true;
            }
            if (city.Trim().Length == 0)
            {
                errorProvider1.SetError(txtCity, "City is required.");
                error = true;
            }


            if (mtxtPhone.MaskCompleted == false && mtxtPhone.Text.Length > 0)
            {
                errorProvider1.SetError(mtxtPhone, "Phone is required or invalid");
                error = true;
            }
            if (mtxtFax.MaskCompleted == false)
            {
                errorProvider1.SetError(mtxtFax, "Fax is invalid.");
                error = true;
            }
            if (mtxtCode.MaskCompleted == false && mtxtCode.Text.Length > 0)
            {
                errorProvider1.SetError(mtxtCode, "Postal Code is must 5 number");
                error = true;
            }
            if (cmbCountry.SelectedIndex == -1)
            {
                errorProvider1.SetError(cmbCountry, "You must select country.");
                error = true;
            }
            if (error == true)
            {
                return false;

            }
            errorProvider1.Clear();
            return true;

        }

        void LoadCustomerInfo()
        {
          
            dt = ds.Tables["Sales.Customers"];
            dv = new DataView(dt);
            gvCustomers.DataSource = dt;
            errorProvider1.Clear();
        }

        void addCustomers()
        {

            try
            {
                DataTable dt = ds.Tables["Sales.Customers"];

                DataRow r = dt.NewRow();

                r["companyname"] = txtCopname.Text;
                r["contactname"] = txtConname.Text;
                r["contacttitle"] = txtTitle.Text;
                r["address"] = txtAddress.Text;
                r["city"] = txtCity.Text;
                r["region"] = txtRegion.Text;
                r["postalcode"] = mtxtCode.Text;
                r["country"] = cmbCountry.Text;
                r["phone"] = mtxtPhone.Text;
                r["fax"] = mtxtFax.Text;

                dt.Rows.Add(r);


                da.Update(ds, "Sales.Customers");
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
            //finally
            //{

            //    con.Close();
            //}
        }


        void deleteCustomers()
        {
            con.Open();
            try
            {
               
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "deleteCustomerTable";

                SqlParameter param = new SqlParameter("@custid", SqlDbType.Int);
                param.Value = int.Parse(txtId.Text);
                cmd.Parameters.Add(param);
                
                cmd.ExecuteNonQuery();
                InitializeADOOObject();
                LoadCustomerInfo();

                
               

                MessageBox.Show("Delete successfully!");   
            }
            catch (Exception e)
            {
                //MessageBox.Show(e.Message);
                MessageBox.Show("Sorry ! This items cannot delete");
                MessageBox.Show("Delete Failed!");
            }

            con.Close();

          
        }
            //bool check = true;
            //try
            //{
            //    DataGridViewRow rv = gvCustomers.SelectedRows[0];
            //    dv = new DataView(dt);
            //    dv.RowFilter = string.Format("custid = '{0}'", rv.Cells["custid"].Value.ToString());
            //    dv.Delete(0);
            //    da.Update(dt);
            //}
            //catch (Exception ex)
            //{

            //    MessageBox.Show("Sorry ! This items cannot delete");
            //    MessageBox.Show("Delete Failed!");
            //}

            //dt.RejectChanges();

            //dt = ds.Tables["Sales.Customers"];
            //gvCustomers.DataSource = dt;
        


        void updateCustomers()
        {
            try
            {
                DataTable dt = ds.Tables["Sales.Customers"];
                DataGridViewRow gv = gvCustomers.SelectedRows[0];
                DataRow[] r = dt.Select(string.Format("custid = '{0}'", gv.Cells["custid"].Value.ToString()));
                //MessageBox.Show(r.RowState.ToString());


                r[0]["companyname"] = this.txtCopname.Text;
                r[0]["contactname"] = this.txtConname.Text;
                r[0]["contacttitle"] = this.txtTitle.Text;
                r[0]["address"] = this.txtAddress.Text;
                r[0]["city"] = this.txtCity.Text;
                r[0]["region"] = this.txtRegion.Text;
                r[0]["postalcode"] = this.mtxtCode.Text;
                r[0]["country"] = this.cmbCountry.Text;
                r[0]["phone"] = this.mtxtPhone.Text;
                r[0]["fax"] = this.mtxtFax.Text;
               
                
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }

        }

        void SearchbyName()
        {

            dv = new DataView(dt);
            dv.RowFilter = string.Format("companyname like '%{0}%' ", txtSearchbyName.Text);
            gvCustomers.DataSource = dv;
        }
        void filterbycountry()
        {

            dv = new DataView(dt);
            dv.RowFilter = string.Format("country like '%{0}%' ", cmbFilter.Text);

            gvCustomers.DataSource = dv;
        }
        void filterbycontacttitle()
        {
            dv = new DataView(dt);
            dv.RowFilter = string.Format("contacttitle like '%{0}%' ", cmbContact.Text);
            gvCustomers.DataSource = dv;
        }
       
#endregion



        #region action
        //private void Customers_Load(object sender, EventArgs e)
        //{
        //    errorProvider1.Clear();
        //    LoadCustomerInfo();
        //}

        private void gvCustomers_SelectionChanged(object sender, EventArgs e)
        {
            if (gvCustomers.SelectedRows.Count > 0)
            {
                btnUpdate.Enabled = true;
                btnDelete.Enabled = true;
                btnAdd.Enabled = false;
                btnNew.Enabled = true;
               
                DataGridViewRow r = gvCustomers.SelectedRows[0];
                txtId.Text = r.Cells["custid"].Value.ToString();
                txtCopname.Text = r.Cells["companyname"].Value.ToString();
                txtConname.Text = r.Cells["contactname"].Value.ToString();
                txtTitle.Text = r.Cells["contacttitle"].Value.ToString();
                txtAddress.Text = r.Cells["address"].Value.ToString();
                txtCity.Text = r.Cells["city"].Value.ToString();
                txtRegion.Text = r.Cells["region"].Value.ToString();
                mtxtCode.Text = r.Cells["postalcode"].Value.ToString();
                cmbCountry.Text = r.Cells["country"].Value.ToString();
                mtxtPhone.Text = r.Cells["phone"].Value.ToString();
                mtxtFax.Text = r.Cells["fax"].Value.ToString();
                errorProvider1.Clear();
            }
        }
 

        private void btnNew_Click(object sender, EventArgs e)
        {
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
           
            btnAdd.Enabled = true;


            txtCopname.Clear();
            txtConname.Clear();
            txtTitle.Clear();
            txtAddress.Clear();
            txtCity.Clear();
            txtRegion.Clear();
            mtxtCode.Clear();
            cmbCountry.SelectedIndex = -1;
            mtxtPhone.Clear();
            mtxtFax.Clear();
            errorProvider1.Clear();
            txtSearchbyName.Clear();
            cmbFilter.SelectedIndex = -1;
            cmbContact.SelectedIndex = -1;
            InitializeADOOObject();
            LoadCustomerInfo();

        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            if (validateInput() == false)

                return;

            addCustomers();
            InitializeADOOObject();
            LoadCustomerInfo();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            
           
            if (validateInput() == false)
                return;
            DialogResult rs = MessageBox.Show("Are you sure to update!", 
              "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (rs == DialogResult.Yes){
                updateCustomers();
                da.Update(dt);
                MessageBox.Show("Update Successful!");
                
        }
            
                 

           
          
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            DialogResult rs = MessageBox.Show("Are you sure to update information to database!",
              "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (rs == DialogResult.Yes)
            if (validateInput() == false)
                return;
            deleteCustomers();
            dt = ds.Tables["Sales.Customers"];
            da.Update(dt);
          
             
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            
            
            DialogResult rs = MessageBox.Show("Are you sure to delete!",
              "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (rs == DialogResult.Yes)
                
            deleteCustomers();
            
            
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            SearchbyName();
         
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            DialogResult result;
            result = MessageBox.Show("Are you want to exit?", "Exit", MessageBoxButtons.OKCancel);

            if (result == DialogResult.Cancel)
            {
                e.Cancel = true;
            }
            this.Hide();
            this.Parent = null;
            e.Cancel = true;
        }

        private void btnFilter_Click(object sender, EventArgs e)
        {
            filterbycountry();
         
        }

        private void button1_Click(object sender, EventArgs e)
        {
            filterbycontacttitle();
        }

#endregion 
     
    }
}
      
        
    

